package cn.edu.scau.Jdbc;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Connection;

import cn.edu.scau.Bean.ClassPage;
import cn.edu.scau.Bean.Classes;
import cn.edu.scau.Bean.Course;
import cn.edu.scau.Bean.Student;
import cn.edu.scau.Bean.StudentPage;

public class JdbcUtils {

	private static String driver = "com.mysql.jdbc.Driver";

	private static String url = "jdbc:mysql://127.0.0.1:3306/mydatabase";

	private static String user = "root";

	private static String password = "cx20141142136";

	private static Connection con = null;

	public JdbcUtils() {

	}

	/**
	 * 获得数据库的连接
	 */
	public static void getConnection() {
		try {
			Class.forName(driver);

			con = (Connection) DriverManager.getConnection(url, user, password);

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public Connection getCon() {
		return con;
	}

	/**
	 * 创建三张表，student表，class表，和中间表
	 */
	public static void createTable() {

		try {
			Statement stmt = con.createStatement();

			stmt.execute("CREATE TABLE class (classid BIGINT PRIMARY KEY,classname VARCHAR(64))");

			stmt.execute("CREATE TABLE student (studentid BIGINT PRIMARY KEY,"
					+ "classid BIGINT,studentname VARCHAR(64)," + "birthday DATETIME,"
					+ "CONSTRAINT stu_cn FOREIGN KEY (classid)REFERENCES class (classid))");
			stmt.execute("CREATE TABLE course (courseid BIGINT PRIMARY KEY, coursename VARCHAR(64),grade DOUBLE)");
			stmt.execute("CREATE TABLE connect (pstudentid BIGINT,pcourseid BIGINT)");
			stmt.execute(
					"alter table connect add CONSTRAINT ps_id FOREIGN KEY (pstudentid)REFERENCES student (studentid)"
							+ "on delete cascade on update cascade");
			stmt.execute("alter table connect add CONSTRAINT pc_id FOREIGN KEY (pcourseid)REFERENCES course (courseid)"
					+ "on delete cascade on update cascade");

			System.out.println("建表成功");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 获得班级最大的长度
	 * 
	 * @param bean
	 * @return
	 */
	public static int getAvaibleCount(Object bean) {

		getConnection();
		int count = 0;
		if (bean instanceof StudentPage) {

			try {
				Statement stmt = con.createStatement();
				ResultSet rs = stmt.executeQuery("select count(studentid) as maxcount from student");
				rs.next();
				count = rs.getInt("maxcount");
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		} else if (bean instanceof ClassPage) {

			try {
				Statement stmt = con.createStatement();
				ResultSet rs = stmt.executeQuery("select count(classid) as maxcount from class");
				rs.next();
				count = rs.getInt("maxcount");
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return count;
	}

	/**
	 * 获得班级的信息
	 * 
	 * @param classPage
	 * @return
	 */
	public static List<cn.edu.scau.Bean.Classes> getClasses(ClassPage classPage) {

		getConnection();
		List<cn.edu.scau.Bean.Classes> classes = new ArrayList<cn.edu.scau.Bean.Classes>();

		if (classPage.getCurrentPage() == classPage.getMaxPage()) {
			try {
				PreparedStatement pstmt = con.prepareStatement("select * from class where classid limit ? , ?");

				pstmt.setInt(1, (classPage.getCurrentPage() - 1) * classPage.getPerPage());
				pstmt.setInt(2, classPage.getMaxRow());
				ResultSet rst = pstmt.executeQuery();
				while (rst.next()) {

					Classes classDemo = new Classes();
					classDemo.setClassId(rst.getInt("classid"));
					classDemo.setClassName(rst.getString("classname"));
					classes.add(classDemo);

				}

				pstmt.close();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		} else {

			try {
				PreparedStatement pstmt = con.prepareStatement("select * from class where classid limit ? , ?");

				pstmt.setInt(1, (classPage.getCurrentPage() - 1) * classPage.getPerPage());

				pstmt.setInt(2, classPage.getCurrentPage() * classPage.getPerPage());

				ResultSet rst = pstmt.executeQuery();

				while (rst.next()) {
					Classes classDemo = new Classes();
					classDemo.setClassId(rst.getInt("classid"));
					classDemo.setClassName(rst.getString("classname"));
					classes.add(classDemo);
				}

				pstmt.close();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return classes;
	}

	/**
	 * 获得学生的信息
	 * 
	 * @param classid
	 * @return
	 */
	public static List<Student> getStudents(int classid) {

		getConnection();
		List<Student> students = new ArrayList<Student>();

		try {
			Statement stmt = con.createStatement();

			ResultSet rst = stmt.executeQuery("select * from student where classid=" + classid);

			while (rst.next()) {

				Student student = new Student();

				String birthday = rst.getDate("birthday").toString();
				student.setClassId(classid);
				student.setBirthday(birthday);
				student.setStudentId(rst.getLong("studentid"));
				student.setStudentName(rst.getString("studentname"));
				students.add(student);
			}

			stmt.close();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return students;
	}

	/**
	 * 获得选修课程的信息
	 * 
	 * @param page
	 * @return
	 */
	public static List<Course> getCourse(String studentId) {

		getConnection();
		
		List<Course> courses = new ArrayList<Course>();
		
		try {
			Statement stmt = con.createStatement();

			ResultSet rst = stmt.executeQuery("SELECT * FROM course"
					+ " WHERE courseid IN (SELECT pcourseid FROM "
					+ "connect WHERE pstudentid = " + studentId + ")");

			while (rst.next()) {

				Course course = new Course();
				course.setCourseId(rst.getInt("courseid"));
				course.setCourseName(rst.getString("coursename"));
				course.setGrade(rst.getDouble("grade"));
				courses.add(course);
			}

			stmt.close();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return courses;
	}

}
